PHP配上MySQL实现批量更新插入

单数据小量数据的更新于插入，操作起来简单，不用太考虑SQL语句怎么书写。正常来说，按照条件判断数据是否已经在表中存在，存在更新，不存在插入。如果是N条数据的话，执行的SQL语句最大值为2N条。当N数值不大的时候，可以用这种简单的方式去更新插入。但是当N数据量不可预估，或者很大的时候，这么做肯定不行，在执行这个操作的时候，可能会将MySQL的连接数占完，造成系统资源被消耗完，影响其他操作。

批量插入

以MySQL举例，网络上有很多的SQL例子：
一下为两种批量插入SQL

insert into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)

insert into table （ `column1`,`column2`,`column3` ）select 1,2,3 union all select 2,3,4 union all select 4,5,6

下面是在MySQL5.79中以第二种方式分三次插入2527条数据所用耗时，以供参考

[2018-12-05 10:58:59] 1000 rows affected in 452 ms
[2018-12-05 11:04:54] 1000 rows affected in 389 ms
[2018-12-05 11:06:12] 527 rows affected in 163 ms

批量更新

批量更新也有几种方式，
一：

replace into table （ `column1`,`column2`,`column3` ）values (1,2,3),(2,3,4),(5,6,7)

这种相当于将数据库的数据与需要插入的数据做交集，将交集数据删除再重新插入。表中的自增id会改变，当没有另外的与数据相关的唯一键或表中有其他数据需要积累或与其他表有关联关系的时候，不适用。
这篇文章有详细的介绍

二：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;

这种更新，需要唯一键来限制，同时更新的字段的值限制比较大，不能自定需要更新字段的数值。只能按照一定逻辑去更新，或许可以用于签到计数类似的场景。
这篇文章有详细的介绍

三：

update table set 
column1 = case  when column2 = 1 then 2 else 3 end ,
column3 = case  when column4 = 1 then 2 else 3 end 
where id in (1,2,3,4)

利用了MySQL的case when 函数，可以按照不同的条件去为不同的字段更新不同的值,同时可以给语句限定不同的条件。

下面用PHP实现以下拼装SQL语句过程：

//拼装按条件批量更新SQL语句
protected function handleUpdate($data, $key)
    {
        if (empty($data) || !is_array($data)) {
            return false;
        }

        $keys_array = array_keys(current($data));

//需要被更新的字段
        $update_column = [
            $keys_array[5],
            $keys_array[6],
            $keys_array[8],
            $keys_array[9],
            $keys_array[16],
            $keys_array[14]
        ];

//更新条件
        $vehicle_id = $keys_array[2];
        $body_color = $keys_array[3];
        $interior_color = $keys_array[4];
        $city_id = $keys_array[11];
        $province_id = $keys_array[10];

        $q = "UPDATE ce SET ";
        foreach ($update_column as $update_colum) {
            $q .= ' ' . $update_colum . ' = CASE ';
            foreach ($data as $value) {
                $value[$update_colum] = !isset($value[$update_colum]) || empty($value[$update_colum])
                    ? 0 : $value[$update_colum];
                $q .= ' when ' . $vehicle_id . ' =' . $value[$vehicle_id]
                    . ' and ' . $body_color . ' = "' . $value[$body_color] . '"'
                    . ' and ' . $interior_color . ' = "' . $value[$interior_color] . '"'
                    . ' and ' . $city_id . ' =' . $value[$city_id]
                    . ' and ' . $province_id . ' =' . $value[$province_id] . ' then ' . $value[$update_colum];
            }
            $q .= " ELSE " . $update_colum . " END, ";
        }
        $q = rtrim($q, ", ");
    }

表结构如下

-- auto-generated definition
create table cd
(
  id                int auto_increment
    primary key,
  brand_id          int default '0'              not null
  comment '车辆品牌id',
  model_id          int default '0'              not null
  comment '车辆车型id',
  vehicle_id        int default '0'              not null
  comment '车辆型号id',
  body_color        varchar(50) default ''       not null
  comment '车身外观颜色',
  interior_color    varchar(50) default ''       not null
  comment '车辆内饰颜色',
  guide_price       decimal(8, 2) default '0.00' not null
  comment '指导价',
  province_id       int default '0'              not null
  comment '省份id',
  province          varchar(50) default ''       not null
  comment '省份名称',
  city_id           int default '0'              not null
  comment '地级市id',
  city              varchar(50) default ''       not null
  comment '地级市名称',
  report_price      decimal(8, 2) default '0.00' not null
  comment '集团报备价',
  average_price     decimal(8, 2) default '0.00' not null
  comment '平均价',
  coefficient       float(5, 2) default '0.00'   not null
  comment '系数',
  price_increase    decimal(8, 2) default '0.00' not null
  comment '加价额',
  operator          int default '0'              not null
  comment '操作者id',
  created_at        int default '0'              not null
  comment '创建时间',
  updated_at        int default '0'              not null
  comment '更新时间',
  constraint unique__index
  unique (vehicle_id, body_color, interior_color, city_id, province_id)
)
  comment '';

判断需要插入与更新的数据

一：保证数据唯一，只需要确保一个值即可
例如，只需要保证身份证唯一，即更新的查询条件只有身份证一个字段的。简单处理可以拿出数组中的值，然后查询数据库，判断是否存在。

$where_in = array_column($array,'column');//全部数据的搜索条件;

$res = mysql_execute('select column from table where column in ('.$where_in.')');//MySQL执行

$res_in = array_column($res,'column');//按搜索条件查找已经在数据库中的数据;

$update_arr = array_diff($where_in,$res_in);//获得不在数据库中的数据

$update_info = array_filter($array,function($item) use($update_arr){
    return in_array($item['column'],$update_arr) ? true : false;
});//获取更新数据

$insert_info = array_filter($array,function($item) use($res_in){
    return in_array($item['column'],$update_arr) ? true : false;
});//获取插入数据

二：多条件筛选
我的解决思路和上面差不多，只是不能从数据库用 where colum in 这样的条件去筛选，主要是考虑到索引的问题，在表数据很大的时候，不合适。

所以利用Redis的set来解决，在set中记录查询条件，只需要遍历数组，判断当前数据中的搜索条件是否在Redis键中即可。

主要是利用第三方来存储条件，当然也可以使用MySQL，保证查询效率即可。

附加优化

一：避免加载的数组太大，占用内存，可以使用yield进行切片，每次输出定量的数据，然后遍历执行。

二：可以使用队列，分批次执行任务。利用Redis或者RabbitMQ等。

三：似乎可以尝试一下swoole的协程，好像很厉害的样子。

以上是我在做项目是遇到的问题与我的思路，有不同的思路或者觉得不对不合适的地方欢迎提出。